# -*- coding: utf-8 -*-

#
# 每年升年级，包括classes和exams中的年级设定
#
import mysql.connector
from mysql.connector import Error
import logging
from datetime import datetime

# 配置日志
logging.basicConfig(
    filename=f"grade_update_{datetime.now().strftime('%Y-%m-%d-%H-%M-%S')}.log",
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    encoding='utf-8'
)

# 配置参数（可根据实际情况修改）
CONFIG = {
    "db_host": "localhost",
    "db_user": "root",
    "db_password": "root",
    "db_name": "school_class",
    "new_classes_count": 6 # 新增班级数量
}

def main():
    current_year = datetime.now().year  # 获取当前年份（如2025）
    db = None
    cursor = None

    try:
        # 连接数据库
        db = mysql.connector.connect(
            host=CONFIG["db_host"],
            user=CONFIG["db_user"],
            password=CONFIG["db_password"],
            database=CONFIG["db_name"]
        )
        if not db.is_connected():
            logging.error("数据库连接失败")
            raise Error("数据库连接失败")
        
        cursor = db.cursor()

        # 检查classes表是否已存在当前年份的grade
        check_query = "SELECT COUNT(*) FROM classes WHERE grade = %s"
        cursor.execute(check_query, (current_year,))
        count = cursor.fetchone()[0]

        if count > 0:
            # 已存在当前年份的grade，记录错误并退出
            error_msg = "本年度已进行过年级提升操作，请勿重复。"
            logging.info(error_msg)
            print(error_msg)
            return

        # 1. 更新classes表的grade
        update_classes_query = """
        UPDATE classes 
        SET grade = CASE 
            WHEN grade < 9 THEN grade + 1 
            WHEN grade = 9 THEN %s 
            ELSE grade 
        END
        """
        cursor.execute(update_classes_query, (current_year,))

        # 2. 新增指定数量的班级（grade=6，name=1班至n班）
        new_classes = [
            (f"{i}班", 6) for i in range(1, CONFIG["new_classes_count"] + 1)
        ]
        insert_classes_query = "INSERT INTO classes (name, grade) VALUES (%s, %s)"
        cursor.executemany(insert_classes_query, new_classes)

        # 3. 更新exams表的grade（忽略null和>9的项）
        update_exams_query = """
        UPDATE exams 
        SET grade = CASE 
            WHEN grade < 9 THEN grade + 1 
            WHEN grade = 9 THEN %s 
            ELSE grade 
        END 
        WHERE grade IS NOT NULL
        """
        cursor.execute(update_exams_query, (current_year,))

        # 提交事务
        db.commit()
        msg = "年级提升操作完成，数据已更新"
        logging.info(msg)
        print(msg)

    except Error as e:
        # 数据库错误处理
        error_msg = f"数据库操作失败: {str(e)}"
        logging.error(error_msg)
        print(error_msg)
        if db:
            db.rollback()  # 回滚事务
    except Exception as e:
        # 其他异常处理
        error_msg = f"程序运行出错: {str(e)}"
        logging.error(error_msg)
        print(error_msg)
        if db:
            db.rollback()
    finally:
        # 关闭连接
        if cursor:
            cursor.close()
        if db and db.is_connected():
            db.close()

if __name__ == "__main__":
    main()